/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.CajaM;
import MsSQL.ConMsSQL;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Maryluz
 */
public class CajaC {

    public boolean agregarMovimientoCajaIngreso(CajaM ovM) {

        Connection cnn = ConMsSQL.obtenerConexion();
        try {
            if (cnn != null) {
                String sql = "{call sp_addMovimientoCaja(?,?,?,?,?)}";
                CallableStatement cs = cnn.prepareCall(sql);
                cs.setString(1, ovM.getRepresentante().toUpperCase());
                cs.setString(2, ovM.getConcepto().toUpperCase());
                cs.setDouble(3, ovM.getIngreso());
                cs.setDouble(4, 0);
                cs.setInt(5,ovM.getIdtienda());
                ResultSet rs = cs.executeQuery();
                if (rs != null) {
                    while (rs.next()) {
                        return true;
                    }
                }
                return false;
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }
    
     public boolean agregarMovimientoCajaEgreso(CajaM ovM) {

        Connection cnn = ConMsSQL.obtenerConexion();
        try {
            if (cnn != null) {
                String sql = "{call sp_addMovimientoCaja(?,?,?,?,?)}";
                CallableStatement cs = cnn.prepareCall(sql);
                cs.setString(1, ovM.getRepresentante());
                cs.setString(2, ovM.getConcepto());
                cs.setDouble(3, 0);
                cs.setDouble(4, ovM.getEgreso());
                cs.setInt(5,ovM.getIdtienda());
                ResultSet rs = cs.executeQuery();
                if (rs != null) {
                    while (rs.next()) {
                        return true;
                    }
                }
                return false;
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }
     
     public List<CajaM> listarIngresos(int idTienda) {
        Connection conexion = ConMsSQL.obtenerConexion();
        if (conexion != null) {
            try {
                List<CajaM> vctCaja = new ArrayList<>();
                String sql = "select idCaja,convert(date,fecha) as fecha , responsable , concepto , ingreso from dbo.TBL_CAJA where egreso=0 and std<>0 and idTienda="+idTienda+" order by idCaja desc";
                Statement instruccionSQL = conexion.createStatement();
                ResultSet rs = instruccionSQL.executeQuery(sql);
                if (rs != null) {
                    while (rs.next()) {
                        CajaM oM = new CajaM();
                        oM.setIdcaja(rs.getInt(1));
                        oM.setFecha(rs.getDate(2));
                        oM.setRepresentante(rs.getString(3));
                        oM.setConcepto(rs.getString(4));
                        oM.setIngreso(rs.getDouble(5)); 
                        vctCaja.add(oM);
                    }
                    return vctCaja;
                }
                return null;
            } catch (SQLException e) {
                // System.out.print(e);
                return null;
            } catch (Exception e) {
                //System.out.print(e);
                return null;
            } finally {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    System.out.print("No se puede cerrar la conexion");
                }
            }
        }
        return null;
    }

     
     public List<CajaM> listarIngresos(String fecha, int idTienda) {
        Connection conexion = ConMsSQL.obtenerConexion();
        if (conexion != null) {
            try {
                List<CajaM> vctCaja = new ArrayList<>();
                String sql = "select idCaja,convert(date,fecha) as fecha , responsable , concepto , ingreso from dbo.TBL_CAJA where egreso=0 and std<>0  and idTienda="+idTienda+" and convert(date,fecha )='"+fecha+"' order by idCaja desc";

                Statement instruccionSQL = conexion.createStatement();
                ResultSet rs = instruccionSQL.executeQuery(sql);

                if (rs != null) {
                    while (rs.next()) {
                        CajaM oM = new CajaM();
                        oM.setIdcaja(rs.getInt(1));
                        oM.setFecha(rs.getDate(2));
                        oM.setRepresentante(rs.getString(3));
                        oM.setConcepto(rs.getString(4));
                        oM.setIngreso(rs.getDouble(5)); 
//                        oM.setPrecioMayor(rs.getDouble(5)); 
                        //System.out.println(rs.getDouble(5));
                        vctCaja.add(oM);
                    }
                    return vctCaja;
                }
                return null;
            } catch (SQLException e) {
                // System.out.print(e);
                return null;
            } catch (Exception e) {
                //System.out.print(e);
                return null;
            } finally {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    System.out.print("No se puede cerrar la conexion");
                }
            }
        }
        return null;
    }

     public List<CajaM> listarEgresos(String fecha, int idTienda) {
        Connection conexion = ConMsSQL.obtenerConexion();
        if (conexion != null) {
            try {
                List<CajaM> vctCaja = new ArrayList<>();
                String sql = "select idCaja,convert(date,fecha) as fecha , responsable , concepto , egreso from dbo.TBL_CAJA where ingreso=0 and std<>0  and idTienda="+idTienda+" and convert(date,fecha )='"+fecha+"' order by idCaja desc";

                Statement instruccionSQL = conexion.createStatement();
                ResultSet rs = instruccionSQL.executeQuery(sql);

                if (rs != null) {
                    while (rs.next()) {
                        CajaM oM = new CajaM();
                        oM.setIdcaja(rs.getInt(1));
                        oM.setFecha(rs.getDate(2));
                        oM.setRepresentante(rs.getString(3));
                        oM.setConcepto(rs.getString(4));
                        oM.setEgreso(rs.getDouble(5)); 
//                        oM.setPrecioMayor(rs.getDouble(5)); 
                        //System.out.println(rs.getDouble(5));
                        vctCaja.add(oM);
                    }
                    return vctCaja;
                }
                return null;
            } catch (SQLException e) {
                // System.out.print(e);
                return null;
            } catch (Exception e) {
                //System.out.print(e);
                return null;
            } finally {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    System.out.print("No se puede cerrar la conexion");
                }
            }
        }
        return null;
    }

      public List<CajaM> listarEgresos(int idTienda) {
        Connection conexion = ConMsSQL.obtenerConexion();
        if (conexion != null) {
            try {
                List<CajaM> vctCaja = new ArrayList<>();
                String sql = "select idCaja,convert(date,fecha) as fecha , responsable , concepto , egreso from dbo.TBL_CAJA where ingreso=0 and std<>0  and idTienda="+idTienda+" order by idCaja desc";

                Statement instruccionSQL = conexion.createStatement();
                ResultSet rs = instruccionSQL.executeQuery(sql);

                if (rs != null) {
                    while (rs.next()) {
                        CajaM oM = new CajaM();
                        oM.setIdcaja(rs.getInt(1));
                        oM.setFecha(rs.getDate(2));
                        oM.setRepresentante(rs.getString(3));
                        oM.setConcepto(rs.getString(4));
                        oM.setEgreso(rs.getDouble(5)); 
//                        oM.setPrecioMayor(rs.getDouble(5)); 
                        //System.out.println(rs.getDouble(5));
                        vctCaja.add(oM);
                    }
                    return vctCaja;
                }
                return null;
            } catch (SQLException e) {
                // System.out.print(e);
                return null;
            } catch (Exception e) {
                //System.out.print(e);
                return null;
            } finally {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    System.out.print("No se puede cerrar la conexion");
                }
            }
        }
        return null;
    }

      public boolean deleteMovimientoCajaIngreso(CajaM ovM) {

        Connection cnn = ConMsSQL.obtenerConexion();
        try {
            if (cnn != null) {
                String sql = "{call sp_delMovimientoCaja(?)}";
                CallableStatement cs = cnn.prepareCall(sql);
                cs.setInt(1, ovM.getIdcaja());
               // System.out.print(ovM.getIdcaja()); 
                ResultSet rs = cs.executeQuery();
                if (rs != null) {
                    while (rs.next()) {
                        return true;
                    }
                }
                return false;
            }
        } catch (Exception e) {
           //  System.out.print(e);
            return false;
        }
        return false;
    }
   
      public boolean deleteMovimientoCajaEgreso(CajaM ovM) {

        Connection cnn = ConMsSQL.obtenerConexion();
        try {
            if (cnn != null) {
                String sql = "{call sp_delMovimientoCaja(?)}";
                CallableStatement cs = cnn.prepareCall(sql);
                cs.setInt(1, ovM.getIdcaja());
               // System.out.print(ovM.getIdcaja()); 
                ResultSet rs = cs.executeQuery();
                if (rs != null) {
                    while (rs.next()) {
                        return true;
                    }
                }
                return false;
            }
        } catch (Exception e) {
           //  System.out.print(e);
            return false;
        }
        return false;
    }
   
}
